﻿Imports System
Imports System.Collections
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmImportTonDauTuExcel
    Dim ctrlTonNganSach As New CTonNganSachController
    Dim ctrlDanhMuc As New CDanhMucController()
    Private Sub btImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btImport.Click
        If (txtFilePath.Text = "") Then
            MessageBox.Show("Chưa chọn file!")
            Exit Sub
        End If
        'kiểm tra đã nhập các thông tin về định dạng Excel hay chưa
        'kiểm tra nhập số thứ tự các cột

        'thực hiện import
        Dim sLoiGhiDuLieu As String = ""
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(txtFilePath.Text)
        Dim tenFile As String
        Dim tenFileLoi As String
        tenFile = FileFunction.GetFileName(txtFilePath.Text)
        tenFileLoi = tenFile.Substring(0, tenFile.LastIndexOf("."))

        If (txtSheet.Text <> "") Then
            xlSheet = xlBook.Sheets(txtSheet.Text)
        Else
            xlSheet = xlBook.Sheets(1)
        End If
        Try
            Dim tuDong As Integer = 2
            If (txtTuDong.Text <> "") Then
                tuDong = Integer.Parse(txtTuDong.Text)
            End If

            Dim denDong As Integer = xlSheet.Range("A65536").End(Excel.XlDirection.xlUp).Row
            'sử dụng Progress Bar
            Me.Height = 264
            Dim max As Integer = denDong - 1
            Me.prgBarProcess.Maximum = max
            Dim strValues As String = ""
            For row As Integer = tuDong To denDong
                prgBarProcess.Value = row - 2
                strValues = "Đang ở dòng " + (row - 2).ToString + ": " + Math.Floor((((row - 2) / max)) * 100).ToString + "%"
                Application.DoEvents()
                lblPercent.Text = strValues
                LuuThongTinChungTu(xlSheet, row, sLoiGhiDuLieu)
            Next
        Catch ex As Exception
            xlBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlBook)
            Throw ex
        End Try

        If (sLoiGhiDuLieu <> "") Then
            FileFunction.Ghi_File(Application.StartupPath & "\Loi_" & tenFileLoi & ".txt", sLoiGhiDuLieu)
            MessageBox.Show("Check xong dữ liệu. Xem file chi tiết")
            System.Diagnostics.Process.Start(Application.StartupPath & "\Loi_" & tenFileLoi & ".txt")
        End If
        Me.Height = 335
        xlBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlBook)
        'releaseObject(xlSheet)
    End Sub
    Public Sub LuuThongTinChungTu(ByVal xlSheet As Excel.Worksheet, ByVal i As Integer, ByRef sLoiGhiDuLieu As String)
        Dim _coLoi As Boolean = False
        Dim sLoi_i As String = "Chứng từ dòng " & i.ToString & " sai:"

        Dim muc As String = ""
        Dim tieumuc As String = ""
        Dim tietmuc As String = ""
        Dim nganh As String = ""
        Dim ma As String = ""
        muc = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 1)).Text.ToString.Trim
        tieumuc = xlSheet.Range(xlSheet.Cells(i, 2), xlSheet.Cells(i, 2)).Text.ToString.Trim
        tietmuc = xlSheet.Range(xlSheet.Cells(i, 3), xlSheet.Cells(i, 3)).Text.ToString.Trim
        nganh = xlSheet.Range(xlSheet.Cells(i, 4), xlSheet.Cells(i, 4)).Text.ToString.Trim
        ma = muc & "-" & tieumuc & "-" & tietmuc & "-" & nganh
        Dim dm As tblDanhMuc = ctrlDanhMuc.SelectRecordByCode(ma)
        If (dm Is Nothing) Then
            dm = New tblDanhMuc
            With dm
                .Muc = muc
                .TieuMuc = tieumuc
                .TietMuc = tietmuc
                .Nganh = nganh
                .Ma = ma
                .NoiDung = xlSheet.Range(xlSheet.Cells(i, 5), xlSheet.Cells(i, 5)).Text.ToString.Trim
            End With
            ctrlDanhMuc.InsertRecord(dm)
        End If
        Dim tuChi As Double = 0
        Dim hienvat As Double = 0
        Dim bvTuCanDoi As Double = 0

        Try
            If (xlSheet.Range(xlSheet.Cells(i, 6), xlSheet.Cells(i, 6)).Text.ToString <> "") Then
                tuChi = CDbl(xlSheet.Range(xlSheet.Cells(i, 6), xlSheet.Cells(i, 6)).Value.ToString())
            End If
        Catch ex As Exception
            sLoi_i &= ", số tiền tự chi"
            _coLoi = True
        End Try

        Try
            If (xlSheet.Range(xlSheet.Cells(i, 7), xlSheet.Cells(i, 7)).Text.ToString <> "") Then
                hienvat = CDbl(xlSheet.Range(xlSheet.Cells(i, 7), xlSheet.Cells(i, 7)).Value.ToString())
            End If
        Catch ex As Exception
            sLoi_i &= ", số tiền hien vat"
            _coLoi = True
        End Try

        Try
            If (xlSheet.Range(xlSheet.Cells(i, 8), xlSheet.Cells(i, 8)).Text.ToString <> "") Then
                bvTuCanDoi = CDbl(xlSheet.Range(xlSheet.Cells(i, 8), xlSheet.Cells(i, 8)).Value.ToString())
            End If
        Catch ex As Exception
            sLoi_i &= ", số tiền BV tu can doi"
            _coLoi = True
        End Try

        If (_coLoi = False) Then
            Dim ton As CTonNganSachInfo = New CTonNganSachInfo
            With ton
                .BVCanDoi = bvTuCanDoi
                .HienVat = hienvat
                .Khac = 0
                .TuChi = tuChi
                .Ma = dm.Ma
                .ID_DanhMuc = dm.ID_DanhMuc
            End With
            ctrlTonNganSach.InsertRecord(ton, cboDonVi.SelectedValue)
        Else
            sLoiGhiDuLieu += vbNewLine + sLoi_i
        End If
    End Sub
    Private Sub btThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btThoat.Click
        Me.Close()
    End Sub

    Private Sub btFileKhac_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btFileKhac.Click
        txtFilePath.Text = ""
    End Sub
    Public Sub DisplayOpenFileDialog()
        Dim openFile As New System.Windows.Forms.OpenFileDialog
        openFile.DefaultExt = ""
        openFile.Filter = "Excel Files(*.xls;*xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"
        openFile.ShowDialog()
        If openFile.FileNames.Length > 0 Then
            Dim filename As String
            For Each filename In openFile.FileNames
                'Load pictureBox
                txtFilePath.Text = filename
            Next
        End If
    End Sub

    Private Sub lkFileExcel_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles lkFileExcel.LinkClicked
        DisplayOpenFileDialog()
    End Sub

    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub frmImportChungTuFromExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Height = 183
        CViewer.BindDonViToComboBox(cboDonVi)
    End Sub

    Private Sub txtSo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDenDong.Leave, txtTuDong.Leave
        Dim txt As TextBox = sender
        CheckONhapSoCot(txt)
    End Sub
    Private Function CheckONhapSoCot(ByVal txt As TextBox) As Boolean
        If (txt.Text <> "" And Integer.TryParse(txt.Text, 0) = False) Then
            MessageBox.Show("Phải nhập số nguyên!")
            txt.Text = ""
            txt.Focus()
            Return False
        Else
            If (txt.Text = "") Then
                txt.Text = "0"
                Return True
            End If
        End If
        Return True
    End Function
End Class